/**
 * Created by Dell on 2017/4/20.
 */
'use strict';
const pg = require('../lib/pgClient');
const log = require('log4js').getLogger('models/users');
const async = require('async');
let sqlStart = "START TRANSACTION;";
let sqlRollback = "ROLLBACK;";
let sqlCommit = "COMMIT;";
let Promise = require('promise');
/**
 * 后台系统管理员新增系统用户
 * @param args
 * @param params
 * @param callback
 */
const add_b_sql1 ='SELECT id FROM users WHERE mobile = $1';//排除重复手机号
const add_b_sql2 = 'INSERT INTO users (headurl, username, mobile, bindtine, follower, reqestnum, concernnum, wantnum, ownnum, status, password, role, desc) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)'
exports.add_b = function(args, params,callback){
    const params1 = [
        args.mobile
    ];
    //查询这个手机号是不是已经注册
    pg.query(add_b_sql1, params1, function(err, result){
        if(err){
            callback (err,null);
        }else if(result.rowCount > 0){//已经注册
            callback (err, 'exit');
        }else {
            pg.query(add_b_sql2, params, function(err, result){//执行添加操作
                if(err){
                    callback (err,null);
                }else {
                    callback (err,result);
                }
            });
        }
    });
};


/**
 *注册用户
 * @param params//数组，传的条件   new_user.mobile,联系方式new_user.password,密码[],关联的设备开始为空0,0,0,0,0,new Date()创建时间,
 * @param new_user传的
 * @param callback
 */
const add_user_sql1 = 'SELECT * FROM users WHERE mobile = $1';//手机号查重
const add_user_sql2 = 'SELECT * FROM users WHERE username = $1';//用户名查重
const add_user_sql3 = 'INSERT INTO users (headurl, username, mobile, bindtine, follower, reqestnum, concernnum, a_wantnum, a_ownnum, e_wantnum, e_ownnum, p_wantnum, p_ownnum, status, password, role, "desc", sex, birthday, address, email, wxinfo) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22)';
exports.add_user = function(params, new_user, callback) {
    const params2 = [new_user.username];
    const params1 = [new_user.mobile];
    pg.query(add_user_sql1, params1, function (err, result) {//手机号查重
        if (err) {
            callback(err, null);
        } else {
            if (result.rowCount == 0) {
                pg.query(add_user_sql2,params2,function(err,result){//用户名查重
                    if(err){
                        callback(err, null);
                    } else if(result.rowCount > 0 ){
                        callback(err,'exit');
                    }else {
                        pg.query(add_user_sql3,params,function(err,result){//添加用户
                            if(err || result.rowCount == 0){
                                callback(err, null);
                            }else {
                                callback (err, result);
                            }
                        });
                    }
                })
            }else{
                callback(err,null);
            }
        }
    });
};

/**
 * app激活已有账号
 * @param params
 * @param callback
 */
const active_sql1 = 'SELECT * FROM users WHERE mobile = $1';
const active_sql2 = 'UPDATE users SET password = $1, status = $2 WHERE mobile = $3';
exports.active_user = function(params, new_user, callback){
    const params1 = [
        new_user.mobile
    ];
    pg.query(active_sql1, params1,function(err, result){
        if(err){
            callback (err,null);
        }else if(result.rowCount == 0){//未注册
            callback (err,null);
        }else if(result.rows[0].password){//账号已经激活
            callback (err,'exit');
        }else {//执行激活
            pg.query(active_sql2, params, function(err,result){
                if(err){
                    callback (err,null);
                }else {
                    callback (err,result);
                }
            })
        }
    });
};
/**
 * 后台多条件查询用户列表
 * @param params
 * @param args
 * @param callback
 */
exports.list_b = function(params, args, callback){
    let sql = 'SELECT id, headurl, username, mobile, bindtine, follower, reqestnum, concernnum, p_wantnum, p_ownnum, a_wantnum, a_ownnum, e_wantnum, e_ownnum, status FROM users WHERE 1=1';
    let number = 1;
    if(args.status){//状态搜索
        sql += ' AND status = $'+number;
        number++;
    };
    if(args.keyword){//关键字搜索
        sql += ' AND (username like $'+number+' OR mobile like $'+number+' OR "desc" like $'+number+')';
        number++;
    };
    if(args.createtime == 'new'){
        sql +=' AND  bindtine > $'+number+' AND  bindtine < $'+(number+1);
        number++;
        number++;
    }
    if(args.createtime == 'yesterday'){
        sql +=' AND  bindtine > $'+number+' AND  bindtine < $'+(number+1);
        number++;
        number++;
    }
    if(args.createtime == 'weak'){
        sql +=' AND  bindtine > $'+number+' AND  bindtine < $'+(number+1);
        number++;
        number++;
    }
    if(args.createtime == 'month'){
        sql +=' AND  bindtine > $'+number+' AND  bindtine < $'+(number+1);
        number++;
        number++;
    }
    sql +=' ORDER BY bindtine DESC LIMIT $'+number+' OFFSET $'+(number+1);
    pg.query(sql, params, function(err,result){
        if (err) {
            callback (err, null);
        }else {
            callback (err, result);
        }
    })
};/**
 * 后台多条件查询用户列表数据统计
 * @param params
 * @param args
 * @param callback
 */
exports.count_b = function(params, args, callback){
    let sql = 'SELECT count(*) FROM users WHERE 1=1';//求和，统计数据
    let number = 1;
    if(args.status){//状态搜索
        sql += ' AND status = $'+number;
        number++;
    };
    if(args.keyword){//关键字搜索
        sql += ' AND (username like $'+number+' OR mobile like $'+number+' OR "desc" like $'+number+')';
        number++;
    };
    if(args.createtime == 'new'){
        sql +=' AND  bindtine > $'+number+' AND  bindtine < $'+(number+1);
        number++;
        number++;
    }
    if(args.createtime == 'yesterday'){
        sql +=' AND  bindtine > $'+number+' AND  bindtine < $'+(number+1);
        number++;
        number++;
    }
    if(args.createtime == 'weak'){
        sql +=' AND  bindtine > $'+number+' AND  bindtine < $'+(number+1);
        number++;
        number++;
    }
    if(args.createtime == 'month'){
        sql +=' AND  bindtine > $'+number+' AND  bindtine < $'+(number+1);
        number++;
        number++;
    }
    pg.query(sql, params, function(err,result){
        if (err) {
            callback (err, null);
        }else {
            callback (err, result.rows[0].count);//摘取有用信息
        }
    })
};
/**
 * 后台批量编辑用户状态
 * @param args传递的信息
 * @param callback
 */
exports.upd_status = function(args, callback){
    let ids = args.ids;//ids 是数组
    let theids = "'"+ids.join("','")+"'";//将数组ids整理成字符串
    const upd_sql1 = 'UPDATE users SET status = $1 WHERE id IN ('+theids+') AND status = $2';//修改id在传进来的id里的所有的用户状态
    const upd_sql2 = 'UPDATE users SET status = $1 WHERE id IN ('+theids+') AND status != $2';//修改id在传进来的id里的所有的用户状态
    const params = [
        args.status,
        3//未激活的不修改
    ];
    pg.query(upd_sql1, params, function(err, result){//执行查询sql语句
        if(err){
            callback (err,null);//报错
        }else if(result.rowCount==0) {//没有未激活状态用户
            pg.query(upd_sql2, params, function(err, result){//执行编辑sql语句
                if(err){
                    callback (err,null);//报错
                }else {
                    callback (err,result);//返回信息
                }
            })
        }else {
            callback (err,'fail');//有未激活的状态的用户
        }
    })
};
/**
 *前台用户登陆
 * @param params//数组，传的条件   new_user.mobile,联系方式new_user.password
 * @param new_user传的
 * @param callback
 */
const log_in_sql1 = 'SELECT * FROM users WHERE mobile = $1';
exports.log_in = function(params, new_user, callback) {
    const log_in_params1 = [new_user.mobile];
    pg.query(log_in_sql1, log_in_params1, function (err, result) {//执行查询
        if (err) {
            return callback(err, null);
        }
        else if(result.rowCount == 0) {//没有用户
            return  callback(err,null);
        }else if(result.rows[0].password == params[0]){//对比密码
            return  callback(err,result);
        }else if(result.rows[0].status == 2){//用户被禁用
            return  callback(err,'wrong_status');
        }else{
            return callback(err,'wrong_password');//密码错误
        }

    });
};
/**
 *前台刷新token
 * @param params//数组，传的条件   new_user.mobile,联系方式new_user.password
 * @param new_user传的
 * @param callback
 */
const update_token_sql1 = 'SELECT * FROM users WHERE id = $1';//查找用户
exports.update_token = function(params, callback) {
    pg.query(update_token_sql1,params, function (err, result) {//执行查询
        if (err) {
            return callback(err, null);
        }
        else if(result.rowCount == 0) {//没有用户
            return  callback(err,null);
        }else {
            return  callback(err,result);
        }
    });
};
/**
 *批量删除用户信息
 */
exports.remove = function(args, callback){
    let ids = args.ids;//ids 是数组
    let theids = "'"+ids.join("','")+"'";//将数组ids整理成字符串
    const remove_sql1 = 'DELETE FROM users WHERE id IN ('+theids+')';//删除id在theids的用户数据
    const remove_sql2 = 'SELECT id FROM users WHERE id IN ('+theids+')';//查询id在theids的用户数据
    pg.query(remove_sql2, [], function(err,result){//执行查询操作
        if(err){
            callback (err,null);
        }else if(result.rowCount == 0) {//不存在
            callback (err,null);
        }else{
            pg.query(remove_sql1, [], function(err,result) {//执行删除操作
                if (err) {
                    callback(err, null);//失败
                } else {
                    callback (err, result);//执行过删除
                }
            })
        }
    });

};


/**
 *忘记密码
 * @param params//数组，传的条件
 * @param new_user传的
 * @param callback
 */
const lost_password_sql1 = 'SELECT id FROM users WHERE mobile = $1';//有没有这个用户
const lost_password_sql2 = 'UPDATE users SET password=$1 WHERE mobile=$2';//修改密码
exports.lost_password = function(params, args, callback) {
    const lost_password_params1 = [args.mobile];
    pg.query(lost_password_sql1, lost_password_params1, function (err, result) {//执行查询操作
        if (err) {
            return callback(err, null);
        }
        else if(result.rowCount == 0) {//没有这个人
            return  callback(err,null);
        }else {
            pg.query(lost_password_sql2,params,function(err,result){//执行修改操作
                if(err){
                    return callback(err, null);
                } else{
                    return  callback(err,result);//执行过未报错
                }
            });
        }
    });
};

/**
 * 修改用户绑定手机号
 * @param params数组
 * @param callback
 */
exports.change_mobile = function(params, args,  callback) {
    const sql = ' UPDATE users SET mobile=$1 WHERE id = $2';
    const sql1 = 'SELECT id FROM users WHERE mobile = $1 AND id != $2';
    pg.query(sql1,params,function(err,result){
        if(err){
            return callback(err,null);
        }else{
            if(result.rowCount==0){
                pg.query(sql,params,function(err,result){
                    if(err){
                        return callback(err,null);
                    }else{
                        if(result.rowCount==0){
                            return callback(err,null);
                        }else{
                            return callback(err,result);
                        }
                    }
                })
            }else{
                return callback(err,'exit');
            }
        }
    })

};


/**
 * 单个用户详情
 * @param params数组（主要是用户的电话）
 * @param callback
 */
const user_info_sql = 'SELECT * FROM users WHERE id = $1 ';//根据id查用户信息
exports.user_info = function(params,callback) {
    pg.query(user_info_sql,params,function(err,result){
        if(err){
            return callback(err,null);
        }else{
            if(result.rowCount==0){//没有这个用户
                return callback(err,null);
            }else{
                return callback(err,result);//返回信息
            }
        }
    })
};

/**
 * 修改用户信息
 * @param params数组（主要是用户的电话用户信息一项一项进行修改）
 * @param callback
 */
exports.update_one_user = function(params, user_info, callback) {
    let sql="";
    //修改用户头像
    if(user_info.headimgurl){
        sql = ' UPDATE users SET headurl=$1 WHERE id = $2';
    }
    //修改用户昵称
    if(user_info.desc){
        sql = ' UPDATE users SET "desc"=$1 WHERE id = $2';
    }
    //修改用户性别
    if(user_info.sex){
        sql =' UPDATE users SET sex=$1 WHERE id = $2';

    }
    //修改用户生日
    if(user_info.birthday){
        sql = ' UPDATE users SET birthday=$1 WHERE id = $2';

    }
    //修改用户地址
    if(user_info.address){
        sql = ' UPDATE users SET address=$1 WHERE id = $2';

    }
    //修改用户地址
    if(user_info.email){
        sql = ' UPDATE users SET email=$1 WHERE id = $2';

    }
    pg.query(sql,params,function(err,result){
        if(err){
            return callback(err,null);
        }else{
            if(result.rowCount==0){
                return callback(err,null);
            }else{
                return callback(err,result);
            }
        }
    })
};

/**
 * 修改用户名
 * @param params数组（主要是用户的电话用户信息一项一项进行修改）
 * @param callback
 */
const username_sql1 = 'SELECT id FROM users WHERE username = $1 AND id != $2 ';//查找用重复户名
const username_sql2 = 'UPDATE users SET username=$1 WHERE id = $2';//修改用户名
exports.update_username = function(params, callback) {
    pg.query(username_sql1,params,function(err,result){//执行查询操作
        if(err){
            callback(err,null);
        }else if(result.rowCount>0){
            callback(err,'exit');
        }else{
            pg.query(username_sql2,params,function(err,result){//执行查询操作
                if(err){
                    callback(err,null);
                }else if(result.rowCount==0){
                    callback(err,'fail');
                }else{
                    callback(err,result);
                }
            })
        }
    })
};

/**
 * 后台系统首页中的活跃分类数据
 * @param req
 * @param res
 */
exports.rankings = function(req, args, callback){
    let date = new Date();
    let nowYear = date.getFullYear();
    let nowMonth = date.getMonth() + 1;  //注意getMonth从0开始
    let nowDay = date.getDate();
    let d1=nowYear + ',' + nowMonth + ',' + nowDay ;   //将当前日期的年月日整合到d中
    // let d1=nowYear + ',' + nowMonth /*+ ',' + nowDay*/ ;   //将当前日期的年月整合到d中
    let date1 = new Date(d1);   //获取当日00：00：00的标准时间
    let params = [
        date1,
        1
    ]
    var sql;
    let infos = [];
    if(args.type&&args.type!='all'){
        params.push(args.type);
        sql = 'SELECT u.headurl, u."desc", p.username, p.author, count(p.*) as number FROM users u, picture p WHERE u.id = p.author AND p.pushtime > $1 AND u.status = $2 AND p.tid = $3 GROUP BY u.headurl, p.username, p.author, u."desc" ORDER BY number DESC';
    }else {
        sql = 'SELECT u.headurl, u."desc", p.username, p.author, count(p.*) as number FROM users u, picture p WHERE u.id = p.author AND p.pushtime > $1 AND u.status = $2 GROUP BY u.headurl, p.username, p.author, u."desc" ORDER BY number DESC';
    }
    const sql1 = 'SELECT id FROM fans WHERE pid = $1 AND userid = $2';
    pg.query(sql, params, function (err,result){
        if(err){
            return callback(err,null);
        }else{
            let theusers = result.rows;
            let len =  theusers.length;
            let fans = 0;
            let a = 1;
            if(len > 0){
                theusers.forEach(function(one_user, index){
                    let params1 = [
                        one_user.author,
                        req.user.id
                    ];
                    pg.query(sql1, params1, function(err, result){//执行查找粉丝表记录
                        if(err){
                            callback (err, null);//报错
                        }else if(result.rowCount > 0){
                            fans = 1;
                        }
                        let info = {
                            id : one_user.author,
                            headurl : one_user.headurl,
                            username : one_user.username,
                            desc : one_user.desc,
                            number:one_user.number,
                            fans : fans
                        };
                        infos.push(info);
                        if (index == (len-1)){
                            callback (err, infos);
                        }
                    })
                })
            }else {
                callback (err, []);
            }
        }
    });
};
/**
 * app添加用户关注
 * @param params
 * @param callback
 */
const unattracte_sql1 = 'SELECT * FROM users WHERE id = $1';//查是否有要关注的用户
const unattracte_sql2 = 'SELECT * FROM users WHERE id = $1';//查是否有要被关注的用户
const unattracte_sql3 = 'SELECT * FROM fans WHERE pid = $1 AND userid = $2';//查看是否重复关注；
const unattracte_sql4 = 'DELETE FROM fans WHERE id = $1';//插入记录
const unattracte_sql5 = 'UPDATE users SET follower = $1 WHERE id = $2';//修改用户粉丝数
const unattracte_sql6 = 'UPDATE users SET concernnum = $1 WHERE id = $2';//修改用户关注的人的个数
exports.unattracte = function(params, callback){
    const params1 = [
        params[1]//要取消关注的用户id
    ];
    const params2 = [
        params[0]
    ];
    const params3 = [
        params[1],
        params[0]
    ];
    var puser;
    var user;
    pg.query(unattracte_sql1, params1, function(err, result) {//是否有要被关注度的这人个人
        if (err) {
            callback(err, null);//报错
        } else if (result.rowCount == 0) {
            callback(err, 'null_puser');//没有要被关注的人
        } else {
            puser = result.rows[0];
            const params5 = [
                Number(puser.follower) - 1,//粉丝数加1
                puser.id
            ];
            pg.query(unattracte_sql2, params2, function (err, result) {
                if (err) {
                    callback(err, null);
                } else if (result.rowCount == 0) {
                    callback(err, 'null_user');//没有这个用户
                } else {
                    user = result.rows[0];
                    const params6 = [
                        Number(user.concernnum) - 1,//关注的人数量加1
                        user.id
                    ];
                    pg.query(unattracte_sql3, params3, function (err, result) {//是否已经关注过
                        if (err) {
                            callback(err, null);//报错
                        } else if (result.rowCount == 0) {
                            callback(err, 'NULL_logs');//已经关注过这个用户
                        } else {
                            let params4 = [
                                result.rows[0].id
                            ]
                            pg.connect(function (err, client, done) {
                                client.query(sqlStart, function () {
                                    client.query(unattracte_sql4, params4, function (err, result) {//添加关注记录
                                        if (err || result.rowCount == 0) {
                                            log.error('删除关注记录报错');
                                            client.query(sqlRollback, function (errRollback) {
                                                if (errRollback) {
                                                    log.error('添加关注记录报错');
                                                }
                                                done();
                                                callback(err, null);
                                            })
                                        } else {
                                            client.query(unattracte_sql5, params5, function (err, result) {
                                                if (err || result.rowCount == 0) {
                                                    log.error('修改用户粉丝数报错');
                                                    client.query(sqlRollback, function (errRollback) {
                                                        if (errRollback) {
                                                            log.error('修改用户粉丝数报错');
                                                        }
                                                        done();
                                                        callback(err, null);
                                                    })
                                                } else {
                                                    client.query(unattracte_sql6, params6, function (err, result) {
                                                        if (err || result.rowCount == 0) {
                                                            log.error('修改用户关注人数报错');
                                                            client.query(sqlRollback, function (errRollback) {
                                                                if (errRollback) {
                                                                    log.error('修改用户关注人数报错');
                                                                }
                                                                done();
                                                                callback(err, null);
                                                            })
                                                        } else {
                                                            client.query(sqlCommit, function (errCommit) {//提交事件
                                                                if (errCommit) {
                                                                    client.query(sqlRollback, function (errRollback) {
                                                                        if (errRollback) {
                                                                            log.error('回滚失败');
                                                                        }
                                                                        done();
                                                                        callback(err, null);
                                                                    });
                                                                } else {
                                                                    done();
                                                                    return callback(err, result);//关注成功
                                                                }
                                                            })
                                                        }

                                                    })
                                                }
                                            })
                                        }
                                    })
                                })
                            })

                        }
                    })
                }
            })
        }
    })
};
/**
 * app添加用户关注
 * @param params
 * @param callback
 */
const attracted_sql1 = 'SELECT * FROM users WHERE id = $1';//查是否有要被关注的用户
const attracted_sql2 = 'SELECT * FROM users WHERE id = $1';//查是否有要被关注的用户
const attracted_sql3 = 'SELECT * FROM fans WHERE pid = $1 AND userid = $2';//查看是否重复关注；
const attracted_sql4 = 'INSERT INTO fans (userid, pid, createtime) VALUES ($1, $2, $3)';//插入记录
const attracted_sql5 = 'UPDATE users SET follower = $1 WHERE id = $2';//修改用户粉丝数
const attracted_sql6 = 'UPDATE users SET concernnum = $1 WHERE id = $2';//修改用户关注的人的个数
exports.attracted = function(params, callback){
    const params1 = [
        params[1]//要关注的用户id
    ];
    const params2 = [
        params[0]
    ];
    const params3 = [
        params[1],
        params[0]
    ];
    var puser;
    var user;
    pg.query(attracted_sql1, params1, function(err, result) {//是否有要被关注度的这人个人
        if (err) {
            callback(err, null);//报错
        } else if (result.rowCount == 0) {
            callback(err, 'null_puser');//没有要被关注的人
        } else {
            puser = result.rows[0];
            const params5 = [
                Number(puser.follower) + 1,//粉丝数加1
                puser.id
            ];
            pg.query(attracted_sql2, params2, function (err, result) {
                if (err) {
                    callback(err, null);
                } else if (result.rowCount == 0) {
                    callback(err, 'null_user');//没有这个用户
                } else {
                    user = result.rows[0];
                    const params6 = [
                        Number(user.concernnum) + 1,//关注的人数量加1
                        user.id
                    ];
                    pg.query(attracted_sql3, params3, function (err, result) {//是否已经关注过
                        if (err) {
                            callback(err, null);//报错
                        } else if (result.rowCount > 0) {
                            callback(err, 'exit_logs');//已经关注过这个用户
                        } else {
                            pg.connect(function (err, client, done) {
                                client.query(sqlStart, function () {
                                    client.query(attracted_sql4, params, function (err, result) {//添加关注记录
                                        if (err || result.rowCount == 0) {
                                            log.error('添加关注记录报错');
                                            client.query(sqlRollback, function (errRollback) {
                                                if (errRollback) {
                                                    log.error('添加关注记录报错');
                                                }
                                                done();
                                                callback(err, null);
                                            })
                                        } else {
                                            client.query(attracted_sql5, params5, function (err, result) {
                                                if (err || result.rowCount == 0) {
                                                    log.error('修改用户粉丝数报错');
                                                    client.query(sqlRollback, function (errRollback) {
                                                        if (errRollback) {
                                                            log.error('修改用户粉丝数报错');
                                                        }
                                                        done();
                                                        callback(err, null);
                                                    })
                                                } else {
                                                    client.query(attracted_sql6, params6, function (err, result) {
                                                        if (err || result.rowCount == 0) {
                                                            log.error('修改用户关注人数报错');
                                                            client.query(sqlRollback, function (errRollback) {
                                                                if (errRollback) {
                                                                    log.error('修改用户关注人数报错');
                                                                }
                                                                done();
                                                                callback(err, null);
                                                            })
                                                        } else {
                                                                client.query(sqlCommit, function (errCommit) {//提交事件
                                                                    if (errCommit) {
                                                                        client.query(sqlRollback, function (errRollback) {
                                                                            if (errRollback) {
                                                                                log.error('回滚失败');
                                                                            }
                                                                            done();
                                                                            callback(err, null);
                                                                        });
                                                                    } else {
                                                                        done();
                                                                        return callback(err, result);//关注成功
                                                                    }
                                                                })
                                                        }

                                                    })
                                                }
                                            })
                                        }
                                    })
                                })
                            })

                        }
                    })
                }
            })
        }
    })
};
/**
 * app用户详情
 * @param params
 * @param callback
 */
const one_sql = 'SELECT * FROM users WHERE id = $1 AND status = $2';//查用户
const one_sql12 = 'SELECT * FROM fans WHERE pid = $1 and userid = $2';//查是否关注
exports.one_user_info = function(params, req, callback){
    const params1 = [
        params[0],
        req.user.id
    ];
    var fan = 0;
    pg.query(one_sql, params, function(err, result){
        if(err){
            callback (err, null);
        }else if(result.rowCount == 0){
            callback (err, 'null');
        }else {
            let user = result.rows[0];
            pg.query(one_sql12, params1, function(err, result){
                if(err){
                    callback (err, null);
                }else if(result.rowCount == 0){
                   fan = 0;
                   let info = {
                       headurl:user.headurl,
                       username:user.username,
                       mobile:user.mobile,
                       bindtine:user.bindtine,
                       follower:user.follower,
                       reqestnum:user.reqestnum,
                       concernnum:user.concernnum,
                       p_wantnum:user.p_wantnum,
                       p_ownnum:user.p_ownnum,
                       status:user.status,
                       desc:user.desc,
                       sex:user.sex,
                       address:user.address,
                       email:user.email,
                       wxinfo:user.wxinfo,
                       birthday:user.birthday,
                       a_wantnum:user.a_wantnum,
                       a_ownnum:user.a_ownnum,
                       e_wantnum:user.e_wantnum,
                       e_ownnum:user.e_ownnum,
                       fans:fan
                   };
                   callback (err,info);
                }else {
                    fan = 1
                    let info = {
                        headurl:user.headurl,
                        username:user.username,
                        mobile:user.mobile,
                        bindtine:user.bindtine,
                        follower:user.follower,
                        reqestnum:user.reqestnum,
                        concernnum:user.concernnum,
                        p_wantnum:user.p_wantnum,
                        p_ownnum:user.p_ownnum,
                        status:user.status,
                        desc:user.desc,
                        sex:user.sex,
                        address:user.address,
                        email:user.email,
                        wxinfo:user.wxinfo,
                        birthday:user.birthday,
                        a_wantnum:user.a_wantnum,
                        a_ownnum:user.a_ownnum,
                        e_wantnum:user.e_wantnum,
                        e_ownnum:user.e_ownnum,
                        fans:fan
                    };
                    callback (err,info);
                }
            })
        }
    })
};


/**
 * 后台系统首页中的活跃分类数据
 * @param req
 * @param res
 */
exports.top = function(req, args, callback){

    let params = [
        req.user.id,
        1
    ];
    let count = 0;
    let infos = [];
    const sql = 'SELECT u.headurl, u."desc", p.username, p.author, count(p.*) as number FROM users u, picture p WHERE u.id = p.author AND u.id != $1 AND u.status = $2 GROUP BY u.headurl, p.username, p.author, u."desc" ORDER BY number DESC LIMIT 10 OFFSET 0';
    const sql1 = 'SELECT id FROM fans WHERE pid = $1 AND userid = $2';
    pg.query(sql, params, function (err,result){
        if(err){
            return callback(err,null);
        }else{
            pg.connect(function (err, client, done) { //获取数据库链接
                client.query(sqlStart, function () { //开启事务
                    let theusers = result.rows;
                    let len = theusers.length;
                    let fans = 0;
                    let a = 0;
                    if (len > 0) {
                        async.mapSeries(theusers, function (one_user, callback) {//异步执行操作
                                let params1 = [
                                    one_user.author,
                                    req.user.id
                                ];
                                client.query(sql1, params1, function (err, result) {//查是否关注过这个用户
                                    if (err) {
                                        callback(err, null);//报错
                                    } else if (result.rowCount > 0) {
                                        fans = 1;
                                        a++;
                                    } else {
                                        fans = 0;
                                        a++;
                                    }
                                    let info = {
                                        id: one_user.author,
                                        headurl: one_user.headurl,
                                        username: one_user.username,
                                        desc: one_user.desc,
                                        number: one_user.number,
                                        fans: fans
                                    };
                                    infos.push(info);
                                    callback(err, info);
                                });
                                // callback ( null, infos);
                            },
                            function (err, n) { //循环完毕提交事务
                                if (err) {
                                    log.error(err);
                                    log.error('回滚.............');
                                    client.query(sqlRollback, function (errRollback) {
                                        if (errRollback) {
                                            log.error('回滚失败');
                                            log.error(errRollback);
                                        }
                                        done();
                                        callback(errRollback, null);
                                    });
                                } else {
                                    log.debug('提交.............');
                                    client.query(sqlCommit, function (errCommit) {
                                        if (errCommit) {
                                            client.query(sqlRollback, function (errRollback) {
                                                if (errRollback) {
                                                    console.log('回滚失败');
                                                    log.error(errRollback);
                                                }
                                                done(); //关闭链接
                                                callback(errCommit, infos);
                                            });
                                        } else {
                                            done();
                                            callback(errCommit, infos);
                                        }

                                    });
                                }
                            })
                        // pg.query(sql1, params1, function(err, result){//执行查找粉丝表记录
                        //     if(err){
                        //         callback (err, null);//报错
                        //     }else if(result.rowCount > 0){
                        //         fans = 1;
                        //         a ++;
                        //     }else {
                        //         fans = 0;
                        //         a++;
                        //     }
                        //     let info = {
                        //         id : one_user.author,
                        //         headurl : one_user.headurl,
                        //         username : one_user.username,
                        //         desc : one_user.desc,
                        //         number:one_user.number,
                        //         fans : fans
                        //     };
                        //     // infos.push(info);
                        //     callback (err, info);
                        //     // if (a == len){
                        //     //     callback (err, infos);
                        //     // }
                        // })
                        // theusers,forEach(function(one_user, index){
                        //     let params1 = [
                        //         one_user.author,
                        //         req.user.id
                        //     ];
                        //     pg.query(sql1, params1, function(err, result){//执行查找粉丝表记录
                        //         if(err){
                        //             callback (err, null);//报错
                        //         }else if(result.rowCount > 0){
                        //             fans = 1;
                        //             a ++;
                        //         }else {
                        //             fans = 0;
                        //             a++;
                        //         }
                        //         let info = {
                        //             id : one_user.author,
                        //             headurl : one_user.headurl,
                        //             username : one_user.username,
                        //             desc : one_user.desc,
                        //             number:one_user.number,
                        //             fans : fans
                        //         };
                        //         infos.push(info);
                        //         if (a == len){
                        //             callback (err, infos);
                        //         }
                        //     })
                        // })
                        // callback (err, infos);
                    } else {
                        callback(err, infos);
                    }
                })
            })
        }
    });
};
// /**
//  * 后台系统首页中的活跃分类数据
//  * @param req
//  * @param res
//  */
// exports.top = function(req, args, callback){
//
//     let params = [
//         req.user.id,
//         1
//     ];
//     let infos = [];
//     const sql = 'SELECT u.headurl, u."desc", p.username, p.author, count(p.*) as number FROM users u, picture p WHERE u.id = p.author AND u.id != $1 AND u.status = $2 GROUP BY u.headurl, p.username, p.author, u."desc" ORDER BY number DESC LIMIT 10 OFFSET 0';
//     const sql1 = 'SELECT id FROM fans WHERE pid = $1 AND userid = $2';
//     pg.query(sql, params, function (err,result){
//         if(err){
//             return callback(err,null);
//         }else{
//             let theusers = result.rows;
//             let len =  theusers.length;
//             let fans = 0;
//             let a = 0;
//             if(len > 0){
//                 // for(let i =0;i<len;i++){
//                 //     let params1 = [
//                 //         theusers[i].author,
//                 //         req.user.id
//                 //     ];
//                 //     pg.query(sql1, params1, function(err, result){//执行查找粉丝表记录
//                 //         if(err){
//                 //             callback (err, null);//报错
//                 //         }else if(result.rowCount > 0){
//                 //             fans = 1;
//                 //         }else {
//                 //             fans = 0;
//                 //         }
//                 //         let info = {
//                 //             id : theusers[i].author,
//                 //             headurl : theusers[i].headurl,
//                 //             username : theusers[i].username,
//                 //             desc : theusers[i].desc,
//                 //             number:theusers[i].number,
//                 //             fans : fans
//                 //         };
//                 //         infos.push(info);
//                 //         a++;
//                 //         if (a == len){
//                 //             callback (err, infos);
//                 //         }
//                 //     })
//                 // }
//                 // var
//
//                 theusers.forEach(function(one_user, index){
//                     let params1 = [
//                         one_user.author,
//                         req.user.id
//                     ];
//                     pg.query(sql1, params1, function(err, result){//执行查找粉丝表记录
//                         if(err){
//                             callback (err, null);//报错
//                         }else if(result.rowCount > 0){
//                             fans = 1;
//                             a ++;
//                         }else {
//                             fans = 0;
//                             a++;
//                         }
//                         let info = {
//                             id : one_user.author,
//                             headurl : one_user.headurl,
//                             username : one_user.username,
//                             desc : one_user.desc,
//                             number:one_user.number,
//                             fans : fans
//                         };
//                         infos.push(info);
//                         // callback (err, infos);
//                         if (a == len){
//                             callback (err, infos);
//                         }
//                     })
//                 })
//
//                 // theusers,forEach(function(one_user, index){
//                 //     let params1 = [
//                 //         one_user.author,
//                 //         req.user.id
//                 //     ];
//                 //     pg.query(sql1, params1, function(err, result){//执行查找粉丝表记录
//                 //         if(err){
//                 //             callback (err, null);//报错
//                 //         }else if(result.rowCount > 0){
//                 //             fans = 1;
//                 //             a ++;
//                 //         }else {
//                 //             fans = 0;
//                 //             a++;
//                 //         }
//                 //         let info = {
//                 //             id : one_user.author,
//                 //             headurl : one_user.headurl,
//                 //             username : one_user.username,
//                 //             desc : one_user.desc,
//                 //             number:one_user.number,
//                 //             fans : fans
//                 //         };
//                 //         infos.push(info);
//                 //         if (a == len){
//                 //             callback (err, infos);
//                 //         }
//                 //     })
//                 // })
//                 // callback (err, infos);
//             }else {
//                 callback (err, infos);
//             }
//         }
//     });
// };
/**
 * app邀请好友（分享）
 * @param params
 * @param callback
 */
// const share_sql1 = 'SELECT * FROM sharelogs WHERE userid = $1 AND shareapp = $2 AND createtime > $3';//查找今天是否已经通过给方式分享
const share_sql2 = 'INSERT INTO sharelogs (userid, shareapp, content, createtime) VALUES ($1, $2, $3, $4)';//添加分享记录
exports.share = function(params, callback){
    // let date = new Date();
    // let nowYear = date.getFullYear();
    // let nowMonth = date.getMonth() + 1;  //注意getMonth从0开始
    // let nowDay = date.getDate();
    // let d1=nowYear + ',' + nowMonth + ',' + nowDay ;   //将当前日期的年月日整合到d中
    // //let d1=nowYear + ',' + nowMonth /*+ ',' + nowDay*/ ;   //将当前日期的年月日整合到d中
    // let date1 = new Date(d1);   //获取当日00：00：00的标准时间
    // let params1 = [
    //     params[0],
    //     params[1],
    //     date1
    // ];
    // pg.query(share_sql1, params1, function (err, result) {
    //     if(err){
    //         callback (err, null);
    //     }else if(result.rowCount > 0){
    //         callback (err, 'exit');//今天已经分享
    //     }else {
            pg.query(share_sql2, params, function (err, result) {
                if(err){
                    callback (err, null);
                }else if(result.rowCount == 0){
                    callback (err, 'fail');//添加失败
                }else {
                    callback (err, result);//返回结果
                }
            })
        // }
    // })
};
exports.image =  function (text, callback) {
    //var text = req.query.text;
    try {
        var img = qr.image(text,{size :10});
        res.writeHead(200, {'Content-Type': 'image/png'});
        img.pipe(res);
    } catch (e) {
        res.writeHead(414, {'Content-Type': 'text/html'});
        res.end('<h1>414 Request-URI Too Large</h1>');
    }
};

const wx_sql1 = 'SELECT * FROM users WHERE mobile = $1';
const wx_sql2 = 'UPDATE users SET wxinfo = $1 WHERE mobile = $2';
exports.update_wx = function (params, args, callback) {
    const params1 = [
        params[1],
    ]
    pg.query(wx_sql1,params1, function(err,result){
        if(err){
            callback(err,null);
        }else if(result.rowCount ==0){
            callback(err,null);
        }else {
            pg.query(wx_sql2, params, function(err,result){
                if(err){
                    callback(err,null);
                }else if(result.rowCount==0){
                    callback(err,'fail');
                }else {
                    pg.query(wx_sql1,params1, function(err,result){
                        if(err){
                            callback(err,null);
                        }else if(result.rowCount ==0){
                            callback(err,null);
                        }else {
                            let user = result.rows[0];
                            callback(err,user)
                        }
                    })
                }
            });
        }
    });
};


/**
 * 根据微信开放平台的unionid获取用户信息
 * @param unionid
 */
const guosql = "SELECT * FROM users WHERE wxinfo->>'openid' = $1 LIMIT 1";
exports.get_user_openid = function (openid, callback) {
    pg.query(guosql, [openid], function(err,result){
        if(err){
            return callback(err,null);
        }else{
            return callback(err,result);
        }
    });
};

/**
 * 修改用户绑定手机号
 * @param params数组
 * @param callback
 */
exports.bind_mobile = function(params, args,  callback) {
   // const sql = ' UPDATE users SET mobile=$1, password = $2 WHERE id = $3';
    const sql = 'INSERT INTO users (headurl, username, mobile, bindtine, follower, reqestnum, concernnum, a_wantnum, a_ownnum, e_wantnum, e_ownnum, p_wantnum, p_ownnum, status, password, role, "desc", sex, birthday, address, email, wxinfo) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22)';
    const sql1 = 'SELECT * FROM users WHERE mobile = $1';//查手机号是否已经注册
    pg.query(sql1,[params[2]],function(err, result) {
        if (err) {
            return callback(err, null);
        } else if(result.rowCount==0){
                pg.query(sql,params,function(err,result){
                    if(err){
                        return callback(err,null);
                    }else{
                        if(result.rowCount==0){
                            return callback(err,'null');
                        }else{
                            return callback(err,result);
                        }
                    }
                })
        }else{
            callback (err,'exit_mobile')
        }

    })
};
//
// /**
//  * 修改用户绑定手机号
//  * @param params数组
//  * @param callback
//  */
// exports.bind_mobile = function(params, args,  callback) {
//     const sql = ' UPDATE users SET mobile=$1, password = $2 WHERE id = $3';
//     const sql1 = 'SELECT id FROM users WHERE mobile = $1 AND id != $2';
//     const sql2 = 'SELECT * FROM users WHERE id = $1';//看用户有没有绑定手机号
//     pg.query(sql2,[params[2]],function(err,result){//查有没有用户，是否已经绑定手机号
//         if(err){
//             callback(err,null);
//         }else if(result.rowCount == 0){
//             callback (err, 'null');//没有这个用户
//         }else if(result.rows[0].mobile){
//             callback (err, 'exit_mobile');//已经绑定手机号
//         }else{
//             pg.query(sql1,[params[0], params[2]],function(err,result) {
//                 if (err) {
//                     return callback(err, null);
//                 } else {
//                     if(result.rowCount==0){
//                         pg.query(sql,params,function(err,result){
//                             if(err){
//                                 return callback(err,null);
//                             }else{
//                                 if(result.rowCount==0){
//                                     return callback(err,null);
//                                 }else{
//                                     return callback(err,result);
//                                 }
//                             }
//                         })
//                     }else{
//                         return callback(err,'exit');
//                     }
//                 }
//             })
//         }
//     })
//
// };
/**
 * 用户上传手机号
 * @param args
 * @param callback
 */
const up_sql1 = 'SELECT * FROM phone WHERE userid = $1';//查看用户是否已经上传过
const up_sql2 = 'UPDATE phone SET phone = $1 WHERE userid = $2';//修改 以前上传的手机号
const up_sql3 = 'INSERT INTO phone (userid, phone, createtime) VALUES ($1, $2, $3)';//添加通讯录
exports.up_phone = function(args, req, callback){
    let phone = args.phone.split(",");//将字符串转化为数组
    const params1 = [
        req.user.id
    ];
    const params2 = [
        phone,
        req.user.id
    ];
    const params3 = [
        req.user.id,
        phone,
        new Date()
    ];
    pg.query(up_sql1, params1, function(err, result){//看用户是否上传过通讯录
        if(err){
            callback (err, null);//报错
        }else if(result.rowCount > 0){
             pg.query(up_sql2, params2, function(err, result){//修改已上传用户通讯录
                if(err){
                    callback (err, null);//报错
                }else if(result.rowCount == 0){
                    callback (err, 'fail');//上传失败
                }else{
                    callback (err, result);//上传成功
                }
            })
        }else {
            pg.query(up_sql3, params3, function(err, result){//添加通讯录
                if(err){
                    callback (err, null);//报错
                }else if(result.rowCount == 0){
                    callback (err, 'fail');//上传失败
                }else{
                    callback (err, result);//上传成功
                }
            })
        }
    })
};
/**
 * app通讯录好友联系人
 * @param paeams
 * @param req
 * @param callback
 */
const phone_sql1 = 'SELECT * FROM phone WHERE userid = $1';//查看这个用户是否上传过联系人
const phone_sql3 = 'SELECT id FROM fans WHERE pid = $1 AND userid = $2';
exports.phone_user = function(params, req, callback){
    let infos=[];
    pg.query(phone_sql1, [req.user.id], function(err, result){//查看这个用户是否上传过联系人
        if(err){
            callback (err, null);//报错
        }else if(result.rowCount == 0){
            callback (err, 'null');//没有上传联系人
        }else{
            let phones = result.rows[0].phone;
            phones = "'"+phones.join("','")+"'";//将数组phones整理成字符串
            const phone_sql2 = 'SELECT * FROM users WHERE mobile IN ('+phones+') AND status = $1 LIMIT $2 OFFSET $3';//正常状态
            pg.query(phone_sql2, params, function(err, result){//查找用户
                if(err){
                    callback (err, null);//报错
                }else if(result.rowCount == 0){
                    callback (err, []);
                }else {
                    let theusers = result.rows;
                    let len =  theusers.length;
                    let fans = 0;
                    if(len > 0){
                        theusers.forEach(function(one_user, index){
                            let params3 = [
                                one_user.id,
                                req.user.id
                            ];
                            pg.query(phone_sql3, params3, function(err, result){//执行查找粉丝表记录
                                if(err){
                                    callback (err, null);//报错
                                }else if(result.rowCount > 0){
                                    fans = 1;
                                }
                                let info = {
                                    id : one_user.id,
                                    headurl : one_user.headurl,
                                    username : one_user.username,
                                    desc : one_user.desc,
                                    fans : fans
                                };
                                infos.push(info);
                                if (index == (len-1)){
                                    callback (err, infos);
                                }
                            })
                        })
                    }else {
                        callback (err, []);
                    }
                }
            })
        }

    })
};
/**
 * 测试
 * @param callback
 */
exports.li = function(callback){
    let sal = 'SELECT * FROM users LIMIT 5 OFFSET 1';
    return Promise.resolve()
        .then(function(){
            return pg.query(sal, [], function(err, result){})
        }).then(function(users){
            if(users){
                callback (users);
            }else {
                callback ([]);
            }
        }).catch(function(err){
            callback (err)
        })
};